package il.ac.tau.dbcourse.db;

import il.ac.tau.dbcourse.db.annotations.SQLTable;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

@SQLTable(table = "Track")
public class Track extends AbsPersistence {
	private String title;
	private Integer length;
	private Album album;
	private Integer trackNumber;

	public Track(Album album) {
		tableName = PersistenceManager.getTableName(this.getClass());
		this.album = album;
		setDirty(true);
	}

	@Override
	public boolean isDirty() {
		if (super.isDirty())
			return true;
		if (album.isDirty())
			return true;
		return false;
	}

	public Track(Connection conn, Long id) {
		try {
			tableName = PersistenceManager.getTableName(this.getClass());
			PreparedStatement stmt = conn.prepareStatement("select * from "
					+ tableName + " where ID=?");
			stmt.setLong(1, id);

			ResultSet rs = stmt.executeQuery();
			if (!rs.next())
				throw new SQLException();
			title = rs.getString("TITLE");
			length = rs.getInt("LENGTH");
			album = new Album(conn, rs.getLong("ALBUM_ID"));
			this.id = id;
			trackNumber = rs.getInt("TRACK_NUMBER");
			setDirty(false);
			rs.close();
			stmt.close();
			rs = null;
			stmt = null;
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	@Override
	protected Long saveImpl(Connection conn) {
		try {
			if (album.isDirty())
				album.saveImpl(conn);
			PreparedStatement stmt;
			if (id == null) {
				stmt = conn.prepareStatement("insert into " + tableName + " "
						+ "(TITLE,LENGTH,ALBUM_ID,TRACK_NUMBER) "
						+ "values (?,?,?,?)", Statement.RETURN_GENERATED_KEYS);
			} else {
				stmt = conn.prepareStatement("update " + tableName + " set "
						+ "TITLE = ?,LENGTH = ?,ALBUM_ID = ?,TRACK_NUMBER = ? "
						+ "where ID=?");
			}

			stmt.setString(1, title);
			stmt.setInt(2, length);
			stmt.setLong(3, album.getId());
			stmt.setInt(4, trackNumber);

			if (id != null)
				stmt.setLong(5, id);

			stmt.executeUpdate();

			if (id == null) {
				ResultSet rs = stmt.getGeneratedKeys();
				if (rs.next()) {
					String rowId = rs.getString(1);
					id = PersistenceManager.getId(conn, this.getClass(), rowId);
				}
				rs.close();
				rs = null;
			}
			stmt.close();
			stmt = null;

			setDirty(false);
			return id;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}

	public String getTitle() {
		return title;
	}

	public void setTitle(String title) {
		if ((this.title != null) && (title.compareTo(this.title) != 0))
			setDirty(true);
		this.title = title;
	}

	public Integer getLength() {
		return length;
	}

	public String getLengthString() {
		int minutes = (int) (length / 60);
		int seconds = length % 60;

		return minutes + ":" + ((seconds < 10) ? "0" : "") + seconds;
	}

	public void setLength(Integer length) {
		if (length != this.length)
			setDirty(true);
		this.length = length;
	}

	public Album getAlbum() {
		return album;
	}

	public void setAlbum(Album album) throws DBException {
		if (album == null)
			throw new DBException();
		if (album.id != this.album.id)
			setDirty(true);
		this.album = album;
	}

	public Integer getTrackNumber() {
		return trackNumber;
	}

	public void setTrackNumber(Integer trackNumber) {
		if (trackNumber != this.trackNumber)
			setDirty(true);
		this.trackNumber = trackNumber;
	}

	public static Long[] getByTitle(Connection conn, String title)
			throws DBException {
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			stmt = conn.prepareStatement("SELECT ID FROM TRACK,ALBUM"
					+ " WHERE contains (TRACK.TITLE,?)>0 AND"
					+ " TRACK.ALBUM_ID=ALBUM.ID"
					+ " ORDER BY ALBUM.REVISION DESC");
			stmt.setString(1, title);
			rs = stmt.executeQuery();
			ArrayList<Long> results = new ArrayList<Long>();
			while (rs.next()) {
				results.add(rs.getLong("ID"));
			}
	
			Long[] resultArr = new Long[results.size()];
			return results.toArray(resultArr);

		} catch (SQLException e) {
			throw new DBException("Cant get list" + e.getMessage());
		} finally {
			try {
				if (stmt != null)
					stmt.close();
				if (rs != null)
					rs.close();
			} catch (Exception e) {
			}
		}
	}

	public static Long[] getByTitleAlbumArtist(Connection conn,
			String trackTitle, String albumTitle, String artistName)
			throws DBException {
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			stmt = conn.prepareStatement("SELECT TRACK.ID "
					+ "FROM TRACK,ALBUM,ARTIST "
					+ " WHERE contains (TRACK.TITLE,?)>0 AND"
					+ " contains (ALBUM.TITLE,?)>0 AND"
					+ " contains (ARTIST.NAME,?)>0 AND"
					+ " TRACK.ALBUM_ID=ALBUM.ID AND ALBUM.ARTIST_ID=ARTIST.ID"
					+ " ORDER BY ALBUM.REVISION DESC");
			stmt.setString(1, trackTitle);
			stmt.setString(2, albumTitle);
			stmt.setString(3, artistName);
			rs = stmt.executeQuery();
			ArrayList<Long> results = new ArrayList<Long>();
			while (rs.next()) {
				results.add(rs.getLong("ID"));
			}
			Long[] resultArr = new Long[results.size()];
			return results.toArray(resultArr);

		} catch (SQLException e) {
			throw new DBException("Cant get list" + e.getMessage());
		} finally {
			try {
				if (stmt != null )
					stmt.close();
				if (rs != null )
					rs.close();
			} catch (Exception e) {
			}
		}

	}

	public static Long[] getByTitleAlbum(Connection conn, String trackTitle,
			String albumTitle) throws DBException {
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			stmt = conn.prepareStatement("SELECT TRACK.ID "
					+ "FROM TRACK,ALBUM "
					+ " WHERE contains (TRACK.TITLE,?)>0 AND"
					+ " contains (ALBUM.TITLE,?)>0"
					+ " TRACK.ALBUM_ID=ALBUM.ID"
					+ " ORDER BY ALBUM.REVISION DESC");
			stmt.setString(1, trackTitle);
			stmt.setString(2, albumTitle);
			rs = stmt.executeQuery();
			ArrayList<Long> results = new ArrayList<Long>();
			while (rs.next()) {
				results.add(rs.getLong("ID"));
			}
			Long[] resultArr = new Long[results.size()];
			return results.toArray(resultArr);

		} catch (SQLException e) {
			throw new DBException("Cant get list" + e.getMessage());
		} finally {
			try {
				if (stmt != null)
					stmt.close();
				if (rs != null)
					rs.close();
			} catch (Exception e) {
			}
		}
	}

	public static Long[] getByTitleArtist(Connection conn, String trackTitle,
			String artistName) throws DBException {
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			stmt = conn.prepareStatement("SELECT TRACK.ID "
					+ "FROM TRACK,ARTIST,ALBUM "
					+ " WHERE contains (TRACK.TITLE,?)>0 AND"
					+ " contains (ARTIST.NAME,?)>0 AND"
					+ " TRACK.ALBUM_ID=ALBUM.ID AND ALBUM.ARTIST_ID=ARTIST.ID"
					+ " ORDER BY ALBUM.REVISION DESC");
			stmt.setString(1, trackTitle);
			stmt.setString(2, artistName);
			rs = stmt.executeQuery();
			ArrayList<Long> results = new ArrayList<Long>();
			while (rs.next()) {
				results.add(rs.getLong("ID"));
			}
			Long[] resultArr = new Long[results.size()];
			return results.toArray(resultArr);

		} catch (SQLException e) {
			throw new DBException("Cant get list" + e.getMessage());
		} finally {
			try {
				if (stmt != null)
					stmt.close();
				if (rs != null)
					rs.close();
			} catch (Exception e) {
			}
		}
	}

	public boolean isMatched(Connection conn) throws DBException {
		if (id == null)
			return false;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			stmt = conn.prepareStatement("SELECT ID FROM "
					+ PersistenceManager.getTableName(UserTrack.class)
					+ " WHERE TRACK_ID=?");
			stmt.setLong(1, id);
			rs = stmt.executeQuery();
			boolean result = rs.next();
			return result;
		} catch (SQLException e) {
			throw new DBException("Cannot look for matched information");
		} finally {
			try {
				if (stmt != null )
					stmt.close();
				if (rs != null )
					rs.close();
			} catch (Exception e) {
			}
		}

	}

}
